package sqlparser;

import exceptions.CouldNotProcessFunctionDeclarationException;
import modeller.databasedesignmodel.DatabaseDesign;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.update.Update;

import javax.swing.*;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.StringReader;
import java.util.*;
import java.util.regex.Pattern;

import static queped.NeptuneHelperFunctions.isInDebugMode;
import static queped.quepedconstants.Strings.*;


public class SQLParser {


    private DatabaseDesign design;

    private CCJSqlParserManager parser = new CCJSqlParserManager();

    private QuePEDSQLStatementVisitor quePEDSQLStatementVisitor;

    private double D_averageIOTime;
    private int scalingFactor;
    private static final double DB_AVERAGE_FILL_FACTOR = 1;
    private double DB_PAGE_SIZE;

    private boolean isDebug;

    private boolean isDefiningRelations = false;

    private static final String DEFINE_TABLES = "defineTables";
    private static final String DECLARE_NUM_ROWS_EQUALS = DECLARE + " numRows " + VARCHAR_DEFAULT;


    public SQLParser(Scanner s, int scalingFactorForRelations, double dbIOTime,double dbPageSize) {

        isDebug = isInDebugMode();

        this.D_averageIOTime = dbIOTime / 1000;
        this.scalingFactor = scalingFactorForRelations;
        this.DB_PAGE_SIZE = dbPageSize;

        // initialise quePEDstatementVisitor
        quePEDSQLStatementVisitor = new QuePEDSQLStatementVisitor(scalingFactor, D_averageIOTime, DB_PAGE_SIZE, DB_AVERAGE_FILL_FACTOR);

        processSQLFile(s);

        design = new DatabaseDesign(quePEDSQLStatementVisitor.getTransactions(), quePEDSQLStatementVisitor.getRelations(), DB_PAGE_SIZE, D_averageIOTime, DB_AVERAGE_FILL_FACTOR);

        if (isDebug) {
            design.toString();
        }

    }

    public DatabaseDesign getDesign() {
        return design;
    }

    private String removeCommentsAndBindVariables(String sql) {

        // no comments
        String sqlClean = removeSQLComments(sql.trim()).trim();
        if (!sqlClean.endsWith(";")) {
            sqlClean = sqlClean + ";";
        }

        // replace bind variable or parameter declarations, eg ':var' or '$var' with JDBC style '?'
        sqlClean = sqlClean.replaceAll("(:)[A-Za-z0-9_]*", "?");
        return sqlClean;


    }

    private void processSQLFile(Scanner s) {

        int tokenNumber = 0;
        LinkedList<String> unParseable = new LinkedList<String>();

        // tokenise input into a sequence of statements
        while (s.hasNext()) {

            if (isDebug) System.out.println(separator);

            tokenNumber++;

            // get SQL, remove comments and append a semicolon if not already there
            String sql = s.next();
            String sqlNoComments = removeCommentsAndBindVariables(sql);

            if (isDebug) System.out.println("With comments:\n" + sql + "\nWithoutcomments:\n" + sqlNoComments);

            try {

                if (parseSQL(sqlNoComments) && isDebug) System.out.println("Parsed successfully!");

            } catch (JSQLParserException e) {
                unParseable.add(sqlNoComments);
                // produce error message
                if (isDebug) System.out.println("\n\nNot parseable: \n" + sqlNoComments);
                e.printStackTrace();
            } catch (CouldNotProcessFunctionDeclarationException e) {
                e.printStackTrace();
            }

        }
    }

    private boolean parseSQL(String sqlString) throws CouldNotProcessFunctionDeclarationException, JSQLParserException {

        // if transaction, process transaction prevalance and strip off to 'BEGIN' keyword and parse contents
        // as new transaction
        if (isCurrentToken(sqlString, CREATE_FUNCTION)) {

            processFunctionDeclaration(nextSubToken(sqlString, CREATE_FUNCTION));
            // strip off "create function <name> AS <DECLARE blah> BEGIN"
            return parseSQL(nextSubToken(sqlString, SEMICOLON));

        } else if (isCurrentToken(sqlString, BEGIN)) {

            // strip off begin keyword and commence transaction
            return parseSQL(nextSubToken(sqlString, BEGIN));

        } else if (isCurrentToken(sqlString, FOR)) {

            processLoop(nextSubToken(sqlString, FOR));
            // parse loop contents
            return parseSQL(nextSubToken(sqlString, LOOP));

        } else if (isCurrentToken(sqlString, END_LOOP)) {

            // finish loopcontents and finishloop
            quePEDSQLStatementVisitor.finishLoopContents();
            quePEDSQLStatementVisitor.finishLoop();
            return parseSQL(nextSubToken(sqlString, END_LOOP));

        } else if (isCurrentToken(sqlString, IF)) {

            // true branch
            processBranch(nextSubToken(sqlString, IF));
            // parse true branch contents
            quePEDSQLStatementVisitor.startTrueBranch();
            return parseSQL(nextSubToken(sqlString, THEN));

        } else if (isCurrentToken(sqlString, ELSE)) {

            // false branch
            quePEDSQLStatementVisitor.finishTrueBranch();
            quePEDSQLStatementVisitor.startFalseBranch();
            // tokenised else statement
            return parseSQL(nextSubToken(sqlString, ELSE));

        } else if (isCurrentToken(sqlString, END_IF)) {

            // end branch statemnet
            quePEDSQLStatementVisitor.finishFalseBranch();
            quePEDSQLStatementVisitor.finishBranch();
            return parseSQL(nextSubToken(sqlString, END_IF));

        } else if (isCurrentToken(sqlString, DECLARE_BRANCH_PROB)) {

            // extract branch probability declared and add to visitor
            String probability = peekAtImmediateToken(nextSubToken(sqlString, REAL_DEFAULT));
            Double prob = Double.parseDouble(probability.substring(0, probability.length() - 1));
            quePEDSQLStatementVisitor.addBranchProb(prob);

            //don't parse the rest of the declaration
            return parseSQL(nextSubToken(sqlString, SEMICOLON));

        } else if (isCurrentToken(sqlString, DECLARE_MATCHING_ROWS)) {

            // extract numMatchingrows declared and cleanse
            String numMatchingRows = peekAtImmediateToken(nextSubToken(sqlString, VARCHAR_DEFAULT)).replaceAll("(\'|;)", "");

            String[] probs = numMatchingRows.split(",");
            for (int q = 0; q < probs.length; q++) {

                int num = Integer.parseInt(probs[q]);
                quePEDSQLStatementVisitor.addNumMatchingRows(num);

            }

            //don't parse the rest of the declaration
            return parseSQL(nextSubToken(sqlString, SEMICOLON));

        } else if (isCurrentToken(sqlString, END)) {

            if (!isDefiningRelations) {
                // end of transaction
                quePEDSQLStatementVisitor.finishTransaction();
            } else {
                // end of defining relations transaction
                isDefiningRelations = false;
            }
            // don't parse
            return parseSQL(nextSubToken(sqlString, END));

        } else if (sqlString.startsWith(RETURN)) {
            // skip
            return parseSQL(nextSubToken(sqlString, RETURN));
        }

        // if empty string or simply semicolon, continue to next token
        if (sqlString.equals("") || sqlString.equals(";")) {
            return true;
        }

        String residual = nextSubToken(sqlString, SEMICOLON);
        int semicolonIndex = sqlString.indexOf(";");
        sqlString = sqlString.substring(0, semicolonIndex + 1);


        // parse SQL query or CREATE TABLE Statement
        Statement statement = parser.parse(new StringReader(sqlString));

        // else create table

        // use to generate relations and transactions
        if (statement instanceof CreateTable) {

            processCreateTable(statement);

        } else if (statement instanceof Select
                || statement instanceof Insert
                || statement instanceof Update
                || statement instanceof Delete
                ) {

            processSQLQuery(sqlString);

        }

        // signify parsed:
        return parseSQL(residual);

    }

    private void processBranch(String s) {

        quePEDSQLStatementVisitor.finishCurrentSQLSequenceIfNecessary();

        quePEDSQLStatementVisitor.startBranch();

    }

    private void processLoop(String s) {

        // find out number of iterations
        String rangeString = peekAtImmediateToken(nextSubToken(s, IN)).trim();
        String[] range = rangeString.split("\\.");

        int minVal = Integer.parseInt(range[0]);
        int maxVal = Integer.parseInt(range[range.length - 1]);

        int numIterations = maxVal - minVal + 1;

        quePEDSQLStatementVisitor.finishCurrentSQLSequenceIfNecessary();
        quePEDSQLStatementVisitor.startLoop(numIterations);

    }

    private void processFunctionDeclaration(String s) throws CouldNotProcessFunctionDeclarationException {

        if (isDebug) System.out.println("\n\nProcessing function with declaration: \n\n" + s);

        String functionName = peekAtImmediateToken(s);


        if (functionName.length() > 3) {

            // remove function parameters
            String transactionName = functionName.substring(0, functionName.length() - 2);

            if (transactionName.equals(DEFINE_TABLES)) {

                isDefiningRelations = true;
                // extract number of rows for relation and cleanse
                String numRowsForRelation = peekAtImmediateToken(nextSubToken(s, VARCHAR_DEFAULT)).replaceAll("(\'|;)", "");
                String[] probs = numRowsForRelation.split(",");

                quePEDSQLStatementVisitor.setNumRows(probs);

            } else {
                // extract transaction prevalance or rate
                if (nextSubToken(s, "AS").contains("lambda")) {

                    String parameterDeclaration = nextSubToken(s, DECLARE);
                    String parameterValue = nextSubToken(parameterDeclaration, "DEFAULT");

                    parameterValue = parameterValue.substring(0, parameterValue.length() - 1);

                    quePEDSQLStatementVisitor.startTransaction(true, transactionName, Double.parseDouble(parameterValue));
                } else {
                    String parameterValue = nextSubToken(s, DECLARE_PREVALENCE_EQUALS);

                    parameterValue = parameterValue.substring(0, parameterValue.length() - 1);

                    quePEDSQLStatementVisitor.startTransaction(false, transactionName, Integer.parseInt(parameterValue));
                }

            }

        } else {
            throw new CouldNotProcessFunctionDeclarationException(s);
        }

    }


    private String peekAtImmediateToken(String s) {
        String[] split = s.split(" ");

        if (split.length > 0) {
            return split[0];
        } else return "";
    }

    private boolean isCurrentToken(String haystack, String needle) {

        haystack = haystack.trim();
        return (haystack.indexOf(needle) == 0);
    }

    private void processSQLQuery(String queryString) throws JSQLParserException {


        Scanner queryScanner = getNewSQLScanner(queryString);

        // iterate through the statements
        while (queryScanner.hasNext()) {

            // cleanse SQL currentSQLQuery
            String query = removeCommentsAndBindVariables(queryScanner.next());

            // parse currentSQLQuery
            Statement statement = parser.parse(new StringReader(query));

            // visit statement which processes this sql query
            statement.accept(quePEDSQLStatementVisitor);

        }

    }

    public Scanner getNewSQLScanner(String sqlString) {
        Scanner s = new Scanner(sqlString);

        // set delimiter to anything with newlines, semicolons or single line comments
        s.useDelimiter("(;(\r)?\n)|(--\n)");

        return s;
    }

    private void processCreateTable(Statement statement) {

        statement.accept(quePEDSQLStatementVisitor);

    }

    private static String getRelationName(String token) {

        // get getFirst token to get name
        String tableName = token.split(" ")[0];
        return "";
    }


    private String nextSubToken(String token, String currentSubToken) {
        return substringAfter(token, currentSubToken).trim();
    }

    private String substringAfter(String str, String separator) {
        if (isEmpty(str)) {
            return str;
        }
        if (separator == null) {
            return "";
        }
        int pos = str.indexOf(separator);
        if (pos == -1) {
            return "";
        }

        return str.substring(pos + separator.length()).trim();
    }


    private String removeSQLComments(String blah) {

        // multiline comments  eg, "/* this is a comment */"
        Pattern commentPattern = Pattern.compile("/\\*.*?\\*/", Pattern.DOTALL);
        blah = commentPattern.matcher(blah).replaceAll("");

        // single line comments eg, "-- this is a single line comment"
        Pattern singleLinecommentPattern = Pattern.compile("-{2,}.*\n", Pattern.DOTALL);
        blah = singleLinecommentPattern.matcher(blah).replaceAll("");

        return blah;
    }

    public static boolean isEmpty(String str) {
        return str == null || str.length() == 0;
    }


    // for debug purposes
    public static void main(String[] args) {

        System.out.println(PLEASE_ENTER_SQL_FILE_NAME);

        Scanner fileReader = new Scanner(System.in);

        String fileName = fileReader.next();

        File examplesql = new File(fileName);

        Scanner s = null;
        try {
            s = new Scanner(examplesql);
            // set delimiter to anything with newlines, semicolons or single line comments
            s.useDelimiter("(;(\r)?\n)|(--\n)");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }

        SQLParser parser = new SQLParser(s, 10000, 8.814786967,8192);

        DatabaseDesign thisDesign = parser.getDesign();
    }
}